SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[_Stock_GetMax] @DrugId NVARCHAR(20)
AS 
    SELECT  CONVERT(BIGINT, '1') AS StockId ,
            DrugDispo.DrugId ,
            ISNULL(SUM(Stock.Quantity), 0) AS Quantity ,
            1.00 AS UnitPrice ,
            GETDATE() AS ExpDate ,
            '' AS BatchLot ,
            '' AS Remark
    FROM    Stock
            RIGHT OUTER JOIN DrugDispo ON Stock.DrugId = DrugDispo.DrugId
    WHERE   ( DrugDispo.DrugId = @DrugId )
            OR ( @DrugId = 'All' )
    GROUP BY DrugDispo.DrugId
GO
